package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.Grade;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 成绩信息表dao
 * @author Administrator
 *
 */
@SuppressWarnings("rawtypes")
public class GradeDaoImp implements CommnonDao{
	/**
	 * 保存内容
	 * @param value
	 * @return 
	 * @throws Exception
	 */
	@Override
	public long save(Object value) throws Exception {
		//判断类   instanceof 
		Grade grade=null;
		if(value instanceof Grade){
			grade=(Grade)value;
			String sql="INSERT INTO t_grade(stu_id,exam_id,stu_score)";
			sql+="VALUES(?,?,?)";
			long Number=JdbcUtil.insertAuto(sql,grade.getStuId(),
					grade.getExamId(),
					grade.getStuScore()
					);
			return Number;
		}
		return 0;
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Object value) throws Exception {
		Grade grade=null;
		if(value instanceof Grade){
			grade=(Grade)value;
			String sql="UPDATE t_grade SET stu_id=?,exam_id=?,stu_score=?";
			sql+=" WHERE grade_id=?";
			int Number=JdbcUtil.update(sql,grade.getStuId(),
					grade.getExamId(),
					grade.getStuScore(),
					grade.getGradeId()
					);
			return Number;
		}
		return 0;
	}
	/**
	 * 删除内容
	 * 学生表t_student，考试表t_course
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Object value) throws Exception {
		Grade grade=null;
		if(value instanceof Grade){
			grade=(Grade)value;
			String sql="DELETE FROM t_grade WHERE grade_id=?";
			int Number=JdbcUtil.update(sql,grade.getGradeId());
			return Number;
		}
		return 0;
	}
	/**
	 * 查找所有的内容
	 * 学生表t_student，考试表t_course
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Grade> findAll(int pageNo, int pageSize) throws Exception {
		String sql="SELECT  te.course_name ,te.`exam_start_time`AS exam_time,ts.stu_name,tg.stu_score ";
		sql+="FROM  t_exam te,t_student ts,t_grade tg WHERE te.`exam_id`=tg.`exam_id` AND tg.`stu_id`=ts.stu_id ";
		sql+="LIMIT ?,?";
		return JdbcUtil.query(Grade.class, sql,(pageNo-1)*pageSize,pageSize);
	}
	/**
	 * 查找所有的内容
	 * 学生表t_student，考试表t_course
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Grade> findAllByClass(String className,int pageNo, int pageSize) throws Exception {
		String sql="SELECT te.`exam_type_name` AS examTypeName,te.course_name AS courseName ,te.`exam_start_time`AS exam_time,ts.stu_name,tg.stu_score ";
		sql+="FROM  t_exam te,t_student ts,t_grade tg WHERE te.`exam_id`=tg.`exam_id` AND tg.`stu_id`=ts.stu_id ";
		sql+=" AND ts.`stu_class_name` =? LIMIT ?,?";
		return JdbcUtil.query(Grade.class, sql,className,(pageNo-1)*pageSize,pageSize);
	}
	//计算总页数
	public int totalPage(String className,String ss,int pageSize){
		String sql="SELECT COUNT(te.`course_name`)AS totalPage";
		sql+=" FROM t_exam te, t_student ts,t_grade tg WHERE te.`exam_id`=tg.`exam_id` AND ts.`stu_id`=tg.`stu_id` " 
			+" AND ts.`stu_class_name`=? AND (te.`course_name`=? OR te.`exam_type_name`=? OR ts.`stu_name`=?) ";
		
		String s="SELECT COUNT(te.`course_name`)AS totalPage FROM t_exam te, t_student ts,t_grade tg ";
			   s+="WHERE te.`exam_id`=tg.`exam_id` AND ts.`stu_id`=tg.`stu_id` AND ts.`stu_class_name`=?";
		long r;
		try {
			if(ss==null||ss.equals("all"))r = (Long) JdbcUtil.uniqueValue(s,1,className);
			else r = (Long) JdbcUtil.uniqueValue(sql,1,className,ss,ss,ss);
			int page=0;
			if(r%pageSize==0){
				page=(int)r/pageSize;
			}else{
				page=(int)r/pageSize+1;
			}
			return page;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		
	}
	/**
	 * 查找单一实体
	 * 学生表t_student，考试表t_course
	 * @param Key
	 * @throws Exception
	 */
	@Override
	public Grade findByKey(Object Key) throws Exception {
		String sql="select * from t_exam where course_name=? or exam_type_name=?";
		return (Grade) JdbcUtil.uniqueBean(Grade.class, sql, Key,Key);
	}

	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Grade> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT ty.exam_type_name , te.course_name,te.`exam_start_time`AS exam_time,ts.stu_name,tg.stu_score ";
		sql+="FROM t_exam_type ty, t_exam te,t_student ts,t_grade tg WHERE te.`exam_id`=tg.`exam_id` AND te.exam_type_name=ty.exam_type_name ";
		sql+="AND tg.`stu_id`=ts.stu_id AND (te.course_name=?  OR ty.exam_type_name=?) LIMIT ?,?";
		return JdbcUtil.query(Grade.class, sql,key,key,(pageNo-1)*pageSize,pageSize);
	}
	
	@SuppressWarnings("unchecked")
	public List<Grade> findByClass(String className,Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT te.`exam_type_name` AS examTypeName,te.course_name AS courseName ,"
				 + "te.`exam_start_time`AS exam_time,ts.stu_name,tg.stu_score "
				 + "FROM  t_exam te,t_student ts,t_grade tg WHERE te.`exam_id`=tg.`exam_id` " 
				 + "AND tg.`stu_id`=ts.stu_id AND ts.`stu_class_name`=? AND (te.course_name=? OR ts.`stu_name`=? OR te.`exam_type_name`=?) LIMIT ?,?";
		return JdbcUtil.query(Grade.class, sql,className,key,key,key,(pageNo-1)*pageSize,pageSize);
	}
}
